from xlutils import copy
import xlrd
import xlwt
import requests

book = xlrd.open_workbook('用例模板.xlsx')# 打开原excel
sheet = book.sheet_by_index(1)# 读取excel
# new_book = copy.copy(book)# 3.复制一个新的excel，在新的excel上做修改
# copy_sheet = new_book.get_sheet(1)# 4.获取复制后的excel的第0个sheet页

for row in range(1,sheet.nrows):
    url = sheet.cell(row,3).value
    method = sheet.cell(row,4).value
    code = sheet.cell(row,7).value
    data ={'username':'a','password':'b'}

    if sheet.cell(row,6).value:#判断是否有header
        r = requests.method(url, data=data)
    else:
        r = requests.method(url, data=data,header = sheet.cell(row,6).value)

    result = r.json()
    sheet.write(row, 8, result)

    if result == code:
        sheet.write(row, 9, '通过')
    else:
        sheet.write(row, 9, '不通过')

book.save('用例模板.xlsx')
